<?php

class Kro extends Controller 
{

	public $file = null;
	public $excel = null;
	public $sheet_num = 0;

	function index()
	{
		global $USER_LEVEL_BONUS_TXT;
		$users = $this->MUser->getChildrenAndMe(Auth::$user_id);

		$me = $this->MUser->get(Auth::$user_id);

		$users_email_index = array();
		$allreferer_emails = array();
		$all_operations_id = array();
		foreach($users as $k => $user)
		{
			$filters = array('user_id' => $user['id'], 'type' => 'buy');
			$operations = $this->MOper->getList($filters);
			$all_operations_id = array_merge($all_operations_id, array_keys($operations));
			$users_email_index[$user['email']] = $user;
			$allreferer_emails[$user['referer_email']] = $user['referer_email'];
		}

		$email_referers = array();
		foreach($allreferer_emails as $r_email)
		{
			if(isset($users_email_index[$r_email]))
			{
				continue;
			}
			$email_referers[] = $r_email;
		}
		$filters_referers = count($email_referers) ? array('email' => $email_referers) : array('id' => -1);

		$filters_referer_operations = array('type' => 'ref', 'parent_id' => $all_operations_id);

		$filters_operations = array('type' => array('ref', 'buy', 'sell', 'sell.mass', 'modify.buy', 'modify.sell', 'ng', 'patriot', 'arest', 'reg', 'week', 'l*'));

		$this->_createBook();
		$this->_createBalanceSheet('Баланс', array('id' => array_keys($users)), $filters_operations);
		if($me['level'] > '*')
		{
			$filters_sotnik_operations = array('type' => 'l**');
			$this->_createBalanceSheet('Бонусы сотника', array('id' => Auth::$user_id), $filters_sotnik_operations);
		}
		if($me['level'] > '**')
			{
			$filters_sotnik_operations2 = array('type' => 'l***');
			$this->_createBalanceSheet('Бонусы тысячника', array('id' => Auth::$user_id), $filters_sotnik_operations2);
			}
		if($me['level'] > '***')
		{
			$filters_sotnik_operations3 = array('type' => 'l****');
			$this->_createBalanceSheet('Бонусы тёмника', array('id' => Auth::$user_id), $filters_sotnik_operations3);
		}
		$this->_createBalanceSheet('Внешние рефереры', $filters_referers, $filters_referer_operations);
//		$this->_createFinReportSheet();
		$this->_closeBook();
	}
	
	function user($user_id = null)
	{
		if(!$this->MUser->inMyStructure($user_id))
		{
			return;
		}
		$user = $this->MUser->get($user_id);
		$this->_createBook($user['fio']);
		$this->_createBalanceSheet('Баланс', array('id' => $user_id));
		$this->_closeBook();
	}

	function me()
	{
		$this->user(Auth::$user_id);
	}
	
	





	private function _createBook($file_suff = '')
	{
//		error_reporting(0);
		require_once APPPATH.'libraries/PHPExcel.php';
		$this->file = "Баланс".($file_suff ? "-".str_replace(" ", "-", $file_suff) : '')."-".date("d.m.Y").".xls";
		$this->excel = new PHPExcel();
		$this->excel->getProperties()->setCreator("MMMANK")
									 ->setLastModifiedBy("MMMANK")
									 ->setTitle("Balance")
		;
	}

	
	
	private function _createBalanceSheet($sheet_name = 'Баланс', $filter_users = array(), $filter_operations = array())
	{
		global $VALUTA;

		$styleMediumBlackBorderOutline = array(
			'borders' => array(
				'outline' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
					'color' => array('argb' => PHPExcel_Style_Color::COLOR_BLACK),
				),
			),
		);

		$styleYellowFill = array(
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	 			'color' => array(
	 				'argb' => PHPExcel_Style_Color::COLOR_YELLOW
	 			)
	 		)
		);

		$styleYellowFill_Center = $styleYellowFill;
		$styleYellowFill_Center['alignment'] = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		$this->excel->createSheet();
		$this->excel->setActiveSheetIndex($this->sheet_num);
		$this->sheet_num++;
		$this->excel->getActiveSheet()->setTitle($sheet_name);

		$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(52);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(27);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(75);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(60);
		
		$this->excel->getActiveSheet()->getStyle('D:G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//		$this->excel->getActiveSheet()->getStyle('A:I')->getFont()->setName('Times New Roman');

		$this->excel->getActiveSheet()
					->setCellValue('A1', 'Статус участника')
					->setCellValue('B1', 'Ф.И.О., (регион проживания), г.р.')
					->setCellValue('C1', 'Контактные данные: email, skype логин и моб.телефон')
					->setCellValue('D1', 'Дата')
					->setCellValue('E1', 'cумма покупки/продажи')
					->setCellValue('F1', 'Курс $МмМ покупки/продажи')
					->setCellValue('G1', '$МмМ')
					->setCellValue('H1', 'Вид и место хранения средств')
					->setCellValue('I1', 'Дополнительно')
		;

//		$this->excel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('A1:I1')->applyFromArray($styleMediumBlackBorderOutline);






		if(!isset($filter_users['sort']))
		{
			$filter_users['sort'] = 'level desc,fio asc';
		}
		$users = $this->MUser->getList($filter_users);
		$allusers = $this->MUser->getList();
		foreach($users as $k => $user)
		{
			$filter_operations['user_id'] = $user['id'];
			if(!isset($filter_operations['sort']))
			{
				$filter_operations['sort'] = 'num asc,date asc,id asc';
			}
			$user['operations'] = $this->MOper->getList($filter_operations);
			$user['all_mavro'] = $this->sheet_num == 1 ? $this->MOper->getMavroByUser($user['id']) : $this->MOper->getMavroByUser($user['id'], $user['operations']);
			$users[$k] = $user;
		}

		$row_num = 2;
		$user_num = 1;
		$last_row_num = $row_num;
		foreach($users as $k => $user)
		{
			if(!count($user['operations']))
			{
				continue;
			}
			
			// Первая колонка

			$text_cell_0 = $user['id'] == Auth::$user_id ? '' : "№ ".$user_num;
			$this->excel->getActiveSheet()->setCellValue('A'.$row_num, $text_cell_0);
			$row_num_1 = $row_num + 1;
			$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 1), $user['status']);
			if($user['referer_email'])
			{
				$row_num_1 += 2;
				$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 2), 'Реферер:');
				$this->excel->getActiveSheet()->setCellValue('A'.($row_num + 3), $user['referer_email']);
			}

			// Вторая колонка
			$row_num_2 = $row_num + 2;
			$this->excel->getActiveSheet()->setCellValue('B'.($row_num + 1), $user['fio']);
			$this->excel->getActiveSheet()->setCellValue('B'.($row_num + 2), $user['otkuda']);

			// Третья колонка
			$row_num_3 = $row_num;
			if($user['email'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, $user['email']);
			}
			if($user['phone'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "Телефон: ".$user['phone']);
			}
			if($user['skype'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "Skype: ".$user['skype']);
			}
			if($user['icq'])
			{
				$row_num_3++;
				$this->excel->getActiveSheet()->setCellValue('C'.$row_num_3, "ICQ: ".$user['icq']);
			}
			
			// Операции
			$row_num_oper = $row_num;
			foreach($user['operations'] as $oper)
			{
				$is_bonus = $oper['bonus'] && $oper['type'] != 'modify.buy' && $oper['type'] != 'modify.sell';
				$this->excel->getActiveSheet()->setCellValue('D'.$row_num_oper, $oper['day'].'.'.$oper['month'].'.'.$oper['year']);
				if($is_bonus)
				{
					$this->excel->getActiveSheet()->getStyle('D'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('E'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('F'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('G'.$row_num_oper)->applyFromArray($styleYellowFill_Center);
					$this->excel->getActiveSheet()->getStyle('H'.$row_num_oper)->applyFromArray($styleYellowFill);
					$this->excel->getActiveSheet()->getStyle('H'.$row_num_oper)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
				}

				$f_summ = isBuy($oper) ? 'buy' : 'sell';
				$this->excel->getActiveSheet()->setCellValue('E'.$row_num_oper, $oper['summ'].' '.$VALUTA[$oper['valuta']]);
				$summ_color = isBuy($oper) ? PHPExcel_Style_Color::COLOR_DARKGREEN : PHPExcel_Style_Color::COLOR_RED;
				$this->excel->getActiveSheet()->getStyle('E'.$row_num_oper)->getFont()->getColor()->setARGB($summ_color);

				$txt_kurs = $oper['type'] != 'week' && $oper['mavro'] > 0 ? $oper['valuta'].' '.$oper['kurs'].' '.$VALUTA[$oper['mavro_valuta']].' ('.$oper['percent'].'%)' : $oper['percent'].'%';
				$this->excel->getActiveSheet()->setCellValue('F'.$row_num_oper, $txt_kurs);
				$kurs_color = isBuy($oper) ? PHPExcel_Style_Color::COLOR_DARKBLUE2 : PHPExcel_Style_Color::COLOR_BLUE;
				$this->excel->getActiveSheet()->getStyle('F'.$row_num_oper)->getFont()->getColor()->setARGB($kurs_color);

				$f_mavro = isBuy($oper) ? 'buy' : 'sell';
				$f_mavro .= $is_bonus  ? "_yellow" : '';
				$txt_mavro = '';
				if($oper['type'] != 'week' && $oper['mavro'] > 0)
				{
					$txt_mavro = $oper['mavro'].' '.$oper['mavro_valuta'];
					if($oper['depo'])
					{
						$super = $oper['depo'] && substr($oper['percent'], -1) == 5 ? "super" : '';
						$txt_mavro .= '(Dep '.$oper['depo'].'m '.$super.')';
					}
				}
				$this->excel->getActiveSheet()->setCellValue('G'.$row_num_oper, $txt_mavro);
				$this->excel->getActiveSheet()->getStyle('G'.$row_num_oper)->getFont()->getColor()->setARGB($summ_color);

				$f_hranen = $is_bonus ? 'hranen_yellow' : 'cell';
				$this->excel->getActiveSheet()->setCellValue('H'.$row_num_oper, $oper['hranen']);

				$this->excel->getActiveSheet()->setCellValue('I'.$row_num_oper, $oper['dop']);

				$row_num_oper++;
			}
			$row_num_oper--;

			$row_num = max($row_num_1, $row_num_2, $row_num_3, $row_num_oper);


			// Добавление последней строки
			$row_num++;
			$all_balance = 'Общий баланс $МмМ:';
			foreach($user['all_mavro'] as $valuta => $arr_valuta)
			{
				foreach($arr_valuta as $percent => $m)
				{
					$all_balance .= ' '.$m.' '.$valuta.' ('.$percent.'%);';
				}
			}
			$this->excel->getActiveSheet()->setCellValue('D'.$row_num, $all_balance);
			$this->excel->getActiveSheet()->mergeCells('D'.$row_num.':G'.$row_num);
			$row_num++;
			if($user['id'] != Auth::$user_id)
			{
				$user_num++;
			}
			$this->excel->getActiveSheet()->getStyle('A'.$last_row_num.':A'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('B'.$last_row_num.':B'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('C'.$last_row_num.':C'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('D'.$last_row_num.':D'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('E'.$last_row_num.':E'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('F'.$last_row_num.':F'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('G'.$last_row_num.':G'.($row_num - 2))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('H'.$last_row_num.':H'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('I'.$last_row_num.':I'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$this->excel->getActiveSheet()->getStyle('D'.($row_num - 1).':G'.($row_num - 1))->applyFromArray($styleMediumBlackBorderOutline);
			$last_row_num = $row_num;
		}

	}

	private function _createFinReportSheet()
	{
		global $USER_LEVEL;

		$format =& $this->workbook->add_format();

		$worksheet = &$this->workbook->add_worksheet(encode('Выплаты'));
		$worksheet->write_string(0, 0, ' ', $format);
		return;

		$me = $this->MUser->get(Auth::$user_id);
		$fam = reset(explode(" ", $me['fio']));
		$level_str = $USER_LEVEL[$me['level']];

		$format1 =& $this->workbook->add_format();
		$format1->set_fg_color('yellow');
		$format1->set_left(2);
		$format1->set_right(2);
		$format1->set_top(2);
		$format1->set_pattern(1);

		$format2 =& $this->workbook->add_format();
		$format2->set_fg_color('yellow');
		$format2->set_left(2);
		$format2->set_right(2);
		$format2->set_bottom(2);
		$format2->set_pattern(1);

		$format3 =& $this->workbook->add_format();
		$format3->set_left(2);
		$format3->set_right(2);

		$format_lrb =& $this->workbook->add_format();
		$format_lrb->set_bottom(2);
		$format_lrb->set_left(2);
		$format_lrb->set_right(2);

		$worksheet = &$this->workbook->add_worksheet('Финансовый отчёт');

		$worksheet->write_string(2, 0, 'Период', $format1);
		$worksheet->write_string(2, 1, 'ФИО (должность)', $format1);
		$worksheet->write_string(2, 2, 'Общая сумма выплат', $format1);
		$worksheet->write_string(2, 3, 'Бонус рукодителя 5% мавро/рубли', $format1);
		$worksheet->write_string(2, 4, 'Сумма по  таблице КРО', $format1);
		$worksheet->write_string(2, 5, 'Сумма на счёте', $format1);

		$worksheet->write_string(3, 0, '', $format2);
		$worksheet->write_string(3, 1, '', $format2);
		$worksheet->write_string(3, 2, 'RUB/USD/EUR/UAH', $format2);
		$worksheet->write_string(3, 3, '', $format2);
		$worksheet->write_string(3, 4, '', $format2);
		$worksheet->write_string(3, 5, '', $format2);

		$worksheet->write_string(4, 0, date('d.m.Y', time() - 60*60*24*7)." - ".date('d.m.Y'), $format3);
		$worksheet->write_string(4, 1, $fam.' ('.$level_str.')', $format3);
		$worksheet->write_string(4, 2, '', $format3);
		$worksheet->write_string(4, 3, '', $format3);
		$worksheet->write_string(4, 4, '', $format3);
		$worksheet->write_string(4, 5, ' RUB /  USD /  EUR /  UAH', $format3);

		$worksheet->write_string(5, 0, '', $format3);
		$worksheet->write_string(5, 1, '', $format3);
		$worksheet->write_string(5, 2, '', $format3);
		$worksheet->write_string(5, 3, '', $format3);
		$worksheet->write_string(5, 4, '', $format3);
		$worksheet->write_string(5, 5, '', $format3);

		$worksheet->write_string(6, 0, '', $format_lrb);
		$worksheet->write_string(6, 1, '', $format_lrb);
		$worksheet->write_string(6, 2, '', $format_lrb);
		$worksheet->write_string(6, 3, '', $format_lrb);
		$worksheet->write_string(6, 4, '', $format_lrb);
		$worksheet->write_string(6, 5, '', $format_lrb);

		$worksheet->set_column(0, 0, 25);
		$worksheet->set_column(1, 1, 21);
		$worksheet->set_column(2, 2, 48);
		$worksheet->set_column(3, 3, 95);
		$worksheet->set_column(4, 4, 55);
		$worksheet->set_column(5, 5, 48);
	}

	private function _closeBook()
	{
		$this->excel->setActiveSheetIndex(0);
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
		$objWriter->save($this->file);
		header("Content-type: application/vnd.ms-excel");
		header("Content-Disposition: attachment; filename=".$this->file);
		header("Expires: 0");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: public");
		$output = file_get_contents($this->file);
		@unlink($this->file);
		die($output);
	}




	
}

